#! /bin/bash
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi
echo '========================================'
echo '==============全量导入==============='
echo '========================================'
/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
delete from hive.bxg_dm.dm_signup;
insert into  hive.bxg_dm.dm_signup
with tmp as (
    select "year",
           year_month,
           year_month_day,
           case
               when grouping("year", year_month, year_month_day) = 0
                   then 'day'
               when grouping("year", year_month) = 0
                   then 'month'
               when grouping("year") = 0
                   then 'year'
               else 'others' end   as time_type,
           -- 新的分组类型字段
                   case
            when grouping(school_id, origin_type, origin_channel, subject_id,
                          department_id) = 15
                 then 'school' -- 日期+校区
            when grouping(school_id, origin_type, origin_channel, subject_id,
                          department_id) = 7
                then 'school+origintype' -- 日期+校区+学习方式
            when grouping(school_id, origin_type, origin_channel, subject_id,
                          department_id) = 21
                then 'subject+origintype' -- 日期+学科+学习方式
            when grouping( school_id, origin_type, origin_channel, subject_id,
                          department_id) = 5
                then 'school+subject+origintype' -- 日期+校区+学科+学习方式
            when grouping(school_id, origin_type, origin_channel, subject_id,
                          department_id) = 19
                then 'origintype+originchannel' -- 日期+咨询中心+学习方式
            when grouping(school_id, origin_type, origin_channel, subject_id,
                          department_id) = 22
                then 'origintype+department' -- 日期+咨询中心+学习方式
            when grouping(school_id, origin_type, origin_channel, subject_id,
                         department_id) = 23
               then 'origintype' -- 日期+学习方式
            when grouping(school_id, origin_type, origin_channel, subject_id,
                department_id) = 31
                then 'all' -- 日期
           else 'others' end   as group_type_new,
           school_id,
           school_name,
           origin_type,
           subject_id,
           subject_name,
           origin_channel,
           department_id,
           department_name,
           group_type,
           sum(signup_count)    as signup_count,
           sum(relationship_count) as relationship_count,
           sum(valid_clue_count)   as valid_clue_count
    from hive.bxg_dws.dws_signup_daycount
    group by
        grouping sets (
        -- 按年上卷
        ("year", group_type),                                                                  -- 年
        ("year", school_id, school_name, group_type),                                          -- 年+校区
        ("year", school_id, school_name, origin_type, group_type),                             -- 年+校区+学习方式
        ("year", subject_id, subject_name, origin_type, group_type),                           -- 年+学科+学习方式
        ("year", school_id, school_name, subject_id, subject_name, origin_type, group_type),   -- 年+校区+学科+学习方式
        ("year", origin_channel, origin_type, group_type),                                     -- 年+来源渠道+学习方式
        ("year", department_id, department_name, origin_type, group_type),                     -- 年+咨询中心+学习方式
        ("year", origin_type, group_type),                                                     -- 年+学习方式
        -- 按月上卷
        ("year", year_month, group_type),                                                                   -- 月
        ("year", year_month, school_id, school_name, group_type),                                           -- 月+校区
        ("year", year_month, school_id, school_name, origin_type, group_type),                              -- 月+校区+学习方式
        ("year", year_month, subject_id, subject_name, origin_type, group_type),                            -- 月+学科+学习方式
        ("year", year_month, school_id, school_name, subject_id, subject_name, origin_type, group_type),    -- 月+校区+学科+学习方式
        ("year", year_month, origin_channel, origin_type, group_type),                                      -- 月+来源渠道+学习方式
        ("year", year_month, department_id, department_name, origin_type, group_type),                      -- 月+咨询中心+学习方式
        ("year", year_month, origin_type, group_type),                                                      -- 月+学习方式
        -- 按天上卷
        ("year", year_month, year_month_day, group_type),                                                                -- 日期
        ("year", year_month, year_month_day, school_id, school_name, group_type),                                        -- 日期+校区
        ("year", year_month, year_month_day, school_id, school_name, origin_type, group_type),                           -- 日期+校区+学习方式
        ("year", year_month, year_month_day, subject_id, subject_name, origin_type, group_type),                         -- 日期+学科+学习方式
        ("year", year_month, year_month_day, school_id, school_name, subject_id, subject_name, origin_type, group_type), -- 日期+校区+学科+学习方式
        ("year", year_month, year_month_day, origin_channel, origin_type, group_type),                                   -- 日期+来源渠道+学习方式
        ("year", year_month, year_month_day, department_id, department_name, origin_type, group_type),                   -- 日期+咨询中心+学习方式
        ("year", year_month, year_month_day, origin_type, group_type)                                                    -- 日期+学习方式
        )
)
select
        "year",
        year_month,
        year_month_day,
        time_type,
        school_id,
        school_name,
        origin_type,
        subject_id,
        subject_name,
        origin_channel,
        department_id,
        department_name,
        group_type_new,
        signup_count,
        relationship_count,
        valid_clue_count
from tmp
where group_type=group_type_new;"